--SQL Hacks, Hack 34: Calculating the Median
--sqlserver version

--------------
--   setup  --
--------------

CREATE TABLE task
(subject VARCHAR(10)
,minutes INTEGER
);
INSERT INTO task VALUES ('Lisa', 1);
INSERT INTO task VALUES ('Marge', 2);
INSERT INTO task VALUES ('Bart', 3);
INSERT INTO task VALUES ('Homer', 4);
INSERT INTO task VALUES ('Ralph', 90);
--------------------
--       hack     --
--------------------

CREATE TABLE #taskI
(posn    INTEGER
,subject VARCHAR(10)
,minutes INTEGER
);
INSERT INTO #taskI(subject,minutes)
  SELECT subject, minutes
    FROM task
   ORDER BY minutes;
DECLARE @rownum AS INTEGER
SELECT @rownum=0
UPDATE taskI
  SET @rownum=@rownum+1,
      posn = @rownum;
SELECT AVG(minutes) FROM taskI,
             (SELECT COUNT(*) n FROM taskI) t
 WHERE posn IN (FLOOR((n+1)/2), FLOOR(n/2)+1);
----------------
--    tidy    --
----------------

DROP TABLE task;
